<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use think\Model;
use PHPExcel;
use PHPExcel_IOFactory;
//
class Wangdian extends Commonwork
{

	public function manage($page=1,$list=10){
		$lists=Db::table("field")->alias("f")->join("field pf","f.pid=pf.id")->where(["f.is_actived"=>1,"f.is_deleted"=>0])->field("f.id,f.field_name,pf.field_name as pname")->select();
		$this->assign("lists",$lists);
		//获得财年
		$cn=Db::table("cg_cainian")->select();
		$this->assign("cn",$cn);


		$request = Request::instance();

    	
    	$data=$request->post();

    	$where="tf.is_actived=1 and tf.is_deleted=0 ";
    	if($data){
    		if($data["cid"]){
    			$where.="and c.id={$data["cid"]} ";
    		}
    	}else{
    		$data["cid"]=0;
    	}

    	$sum=Db::table("target_net_format")->alias("tf")->join("cg_cainian c","tf.cainian=c.id")->where($where)->count();
    	//echo $sum;exit;
    	
    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

        $apply=Db::table("target_net_format")->alias("tf")->join("cg_cainian c","tf.cainian=c.id")
        ->join("users uc","tf.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->where($where)->order("c.id desc")
        ->limit($start,$list)
        ->field("tf.id,c.title as cainian,ec.em_name as cname,tf.created_on")
        ->select();

         for($i=0;$i<count($apply);$i++){
        	$apply[$i]["created_on"]=date("Y-m-d H:i",strtotime($apply[$i]["created_on"]));
        	
        	
        }
        $this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("data",$data);
		$this->assign("apply",$apply);



		return $this->fetch('manage');
	}
	//按照id获取格式
	public function gettar($fid){
		$sum=Db::table("target_net_format")->alias("tf")->join("cg_cainian c","tf.cainian=c.id")->where(["tf.id"=>$fid])->find();
		return json_encode($sum);
	}
	//新增格式
	public function baocun(){
		 $request=Request::instance();
        $data=$request->post();

        $cid=$data["cainian"];
        $sum=Db::table("target_net_format")->where(["cainian"=>$cid,"is_actived"=>1,"is_deleted"=>0])->count();
        if($sum){
        	//已存在
        	return json_encode(["msg"=>"该财年已设定格式","state"=>2]);
        }else{
        	//不存在新增
	       	if($data["product"])
	        	$typedata["product_id"]=$data["product_id"];
	        else{
	        	$typedata["product_id"]=null;
	        }
	       
	        $typedata["kaoheqi"]=$data["kaoheqi"];
	        $typedata["cainian"]=$cid;
	         $typedata["c_users_id"]=Session::get("login_id");
	        $typedata["created_on"]=date("Y-m-d H:i:s");
	        Db::startTrans(); //开启事物
        	try{
        		$res1=Db::table("target_net_format")->insert($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
            if($res1){
                Db::commit(); //事物提交
                return json_encode(["msg"=>"保存成功","state"=>1]);//成功
            }else{
                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);//失败
            }
        }
	}

	//修改
	public function xiugai(){
		$request=Request::instance();
        $data=$request->post();

        	if($data["product"])
	        	$typedata["product_id"]=$data["product_id"];
	        else{
	        	$typedata["product_id"]=null;
	        }
	       
	        $typedata["kaoheqi"]=$data["kaoheqi"];
	        $typedata["id"]=$data["fid"];
	         $typedata["m_users_id"]=Session::get("login_id");
	        $typedata["modifiled_on"]=date("Y-m-d H:i:s");
	         Db::startTrans(); //开启事物
        	try{
        		$res1=Db::table("target_net_format")->update($typedata);

        	}catch(\Exception $e){

                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
            if($res1){
                Db::commit(); //事物提交
                return json_encode(["msg"=>"保存成功","state"=>1]);//成功
            }else{
                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);//失败
            }
	}
	//所有网点管理
	public function allapmanage($page=1,$list=10){

		$request = Request::instance();

    	
    	$data=$request->post();

    	$where="ta.is_actived=1 and ta.is_deleted=0 ";
    	if($data){
    		if(trim($data["no"]))
    			$where.="and ta.no like '%{$data["no"]}%' ";
    		if(trim($data["title"]))
    			$where.="and ta.title like '%{$data["title"]}%' ";
    		if(trim($data["cainian"]))
    			$where.="and cn.title like '%{$data["cainian"]}%' ";
    		if($data["state"])
                $where.="and ta.state={$data["state"]} ";
    	}else{
    		$data=["no"=>"","title"=>"","cainian"=>"","state"=>0];
    	}

    	$sum=Db::table("target_net_apply")->alias("ta")
    	->join("target_net_format tf","ta.type_id=tf.id")
    	->join("cg_cainian cn","tf.cainian=cn.id")->where($where)->count();
    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

        $apply=Db::table("target_net_apply")->alias("ta")
    	->join("target_net_format tf","ta.type_id=tf.id")
    	->join("cg_cainian cn","tf.cainian=cn.id")
        ->join("users uc","ta.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->where($where)->order("ta.created_on desc,ta.no desc")
        ->limit($start,$list)
        ->field("ta.id,ta.no,ta.title,ta.state,ec.em_name as cname,ta.created_on,cn.title as cainian,ta.state2,ta.tag,ta.islock")
        ->select();

         for($i=0;$i<count($apply);$i++){
        	$apply[$i]["created_on"]=date("Y-m-d H:i",strtotime($apply[$i]["created_on"]));
        	$apply[$i]["statetitle"]=db("cg_ti_hexiao_status")->where("id={$apply[$i]["state"]}")->value("title");
            if($apply[$i]["state2"]==1)
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>25])->value("id");
            else
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>26])->value("id");
        	
        }
        $this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("data",$data);
		$this->assign("apply",$apply);

		return $this->fetch('allapmanage');
	}
    public function tijiao($id){
        $state2=db("target_net_apply")->where(["id"=>$id])->value("state2");
        if($state2==2)
            return $this->gowork(26,$id);
        else
            return $this->gowork(25,$id);
    }
    public function shanchu($id){
        $tag=Db::table("target_net_apply")->where("id=$id")->find();
        Db::startTrans();
        try{
           if($tag["state2"]==2){
                //异动
                Db::table("target_net_apply")->where(["id"=>$tag["yuanid"]])->update(["tag"=>0,"nextid"=>null,"islock"=>0]);
            } 
            $res=Db::table("target_net_apply")->where(["id"=>$id])->update(["is_deleted"=>1]);
        }catch(\Exception $e){
            Db::rollBack();
            return 2;
        }
        if($res){
            Db::commit();
            return 1;
        }
        else{
              Db::rollBack();
            return 2;
        }
    }
	//添加格式
	public function addfor(){
		//查询已经设定过格式的财年
		$cfarr=Db::table("target_net_format")->alias("tf")
		->join("cg_cainian cn","tf.cainian=cn.id")->field("tf.id,cn.title")->select();
		$this->assign("cfarr",$cfarr);
		return $this->fetch('addfor');
	}
	//添加操作
	public function new_add(){
		$request = Request::instance();

    	
    	$data=$request->post();
    	$biaoti=trim(htmlspecialchars($data["title"]));
    	if(!$biaoti){
    		 return json_encode(["msg"=>"主题不能为空","state"=>2]);
    	}
    	
    	$state=trim($data["type"]);
    	$fid=trim($data["fid"]);
    	$file = request()->file('wenjian');

    	if($file){
    		$info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/wangdian' . DS );
	    	if($info){
	            // 成功上传后 获取上传信息
	           
	            // 输出
	            $savename= $info->getSaveName();
	            $path=ROOT_PATH . 'public/uploads/wangdian/'.$savename;
	          	$res=$this->excel_duqu($fid,$state,$biaoti,$path);
	          	return $res;

	          	
	        }else{
	            // 上传失败获取错误信息
	            return json_encode(["msg"=>$file->getError(),"state"=>2]);
	           
	        }
    	}else{
    		 return json_encode(["msg"=>"文件不能为空","state"=>2]);
    	}
    	
    	//echo 1;exit;
		//print_r($data);exit;
	}

	//所有网点查询
	public function allapcha($page=1,$list=10){
		$request = Request::instance();

        
        $data=$request->post();

        $where="ta.is_actived=1 and ta.is_deleted=0 ";
        if($data){
            if(trim($data["no"]))
                $where.="and ta.no like '%{$data["no"]}%' ";
            if(trim($data["title"]))
                $where.="and ta.title like '%{$data["title"]}%' ";
            if(trim($data["cainian"]))
                $where.="and cn.title like '%{$data["cainian"]}%' ";
            if($data["state"])
                $where.="and ta.state={$data["state"]} ";
        }else{
            $data=["no"=>"","title"=>"","cainian"=>"","state"=>0];
        }

        $sum=Db::table("target_net_apply")->alias("ta")
        ->join("target_net_format tf","ta.type_id=tf.id")
        ->join("cg_cainian cn","tf.cainian=cn.id")->where($where)->count();
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

        $apply=Db::table("target_net_apply")->alias("ta")
        ->join("target_net_format tf","ta.type_id=tf.id")
        ->join("cg_cainian cn","tf.cainian=cn.id")
        ->join("users uc","ta.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->where($where)->order("ta.created_on desc,ta.no desc")
        ->limit($start,$list)
        ->field("ta.id,ta.no,ta.title,ta.state,ec.em_name as cname,ta.created_on,cn.title as cainian,ta.state2,ta.tag,ta.islock")
        ->select();

         for($i=0;$i<count($apply);$i++){
            $apply[$i]["created_on"]=date("Y-m-d H:i",strtotime($apply[$i]["created_on"]));
            $apply[$i]["statetitle"]=db("cg_ti_hexiao_status")->where("id={$apply[$i]["state"]}")->value("title");
            if($apply[$i]["state2"]==1)
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>25])->value("id");
            else
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>26])->value("id");
            
        }
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        $this->assign("apply",$apply);

		return $this->fetch('allapcha');
	}

	public function allaptongji($page=1,$list=20){
		$request = Request::instance();

    	
    	$data=$request->post();
        $cainian=db("cg_cainian")->select();
        $this->assign("cainian",$cainian);

    	$where="ta.is_deleted=0 and ta.is_actived=1 and ta.state=7 and ta.tag<>1 ";
    	if($data){
            if($data["fenqu"]){
                $tmp=trim($data["fenqu"]);
                $where.="and z.title='{$tmp}' ";
            }
            if($data["sheng"]){
                $tmp=trim($data["sheng"]);
                $where.="and p.title='{$tmp}' ";
            }
            if($data["shi"]){
                $tmp=trim($data["shi"]);
                $where.="and c.title='{$tmp}' ";
            }
            if($data["jxs"]){
                $tmp=trim($data["jxs"]);
                $where.="and jxs.title='{$tmp}' ";
            }
            if($data["sys"]){
                $tmp=trim($data["sys"]);
                $where.="and s.title='{$tmp}' ";
            }
            if($data["net"]){
                $tmp=trim($data["net"]);
                $where.="and n.title='{$tmp}' ";
            }
            if($data["c1"]){
                $tmp=trim($data["c1"]);
                $where.="and ch1.title='{$tmp}' ";
            }
            if($data["c2"]){
                $tmp=trim($data["c2"]);
                $where.="and ch2.title='{$tmp}' ";
            }
            if($data["cid"]){
                $tmp=trim($data["cid"]);
                $where.="and cn.id={$tmp} ";
            }
            if($data["pinpai"]){
                $tmp=trim($data["pinpai"]);
                $where.="and pp.pro_name='{$tmp}' ";
            }
        }else{
            $data=["fenqu"=>"","sheng"=>"","shi"=>"","jxs"=>"","sys"=>"","net"=>"","c1"=>"","c2"=>"","cid"=>"","pinpai"=>""];
        }
        //$this->assign("data",$data);
    		$sum=Db::table("target_net_detail")->alias("td")
    		->join("target_net_apply ta","td.apply_id=ta.id","LEFT")
    		->join("target_net_format tf","ta.type_id=tf.id","LEFT")
    		->join("cg_cainian cn","tf.cainian=cn.id","LEFT")
    		->join("cg_net n","td.wangdian=n.id","LEFT")
    		->join("cg_market_zoning z","n.zoning=z.id","LEFT")
    		->join("cg_market_provice p","n.provice=p.id","LEFT")
    		->join("cg_market_city c","n.city=c.id","LEFT")
    		->join("cg_system s","n.system_id=s.id","LEFT")
    		->join("cg_jxs jxs","s.jxs=jxs.id","LEFT")
    		->join("cg_channel_types1 ch1","s.oneCType=ch1.id","LEFT")
    		->join("cg_channel_types2 ch2","s.twoCType=ch2.id","LEFT")
            ->join("pro_products pp","td.pinpai=pp.id","LEFT")
    		->where($where)->count();
    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

        $apply=Db::table("target_net_detail")->alias("td")
    		->join("target_net_apply ta","td.apply_id=ta.id","LEFT")
    		->join("target_net_format tf","ta.type_id=tf.id","LEFT")
    		->join("cg_cainian cn","tf.cainian=cn.id","LEFT")
    		->join("cg_net n","td.wangdian=n.id","LEFT")
    		->join("cg_market_zoning z","n.zoning=z.id","LEFT")
            ->join("employee ze","z.head=ze.id","LEFT")
    		->join("cg_market_provice p","n.provice=p.id","LEFT")
            ->join("employee pe","p.head=pe.id","LEFT")
    		->join("cg_market_city c","n.city=c.id","LEFT")
            ->join("employee ce","c.head=ce.id","LEFT")
    		->join("cg_system s","n.system_id=s.id","LEFT")
    		->join("cg_jxs jxs","s.jxs=jxs.id","LEFT")
    		->join("cg_channel_types1 ch1","s.oneCType=ch1.id","LEFT")
    		->join("cg_channel_types2 ch2","s.twoCType=ch2.id","LEFT")
    		->join("cg_provice xp","n.exec_provice=xp.id","LEFT")
    		->join("cg_city xc","n.exec_city=xc.id","LEFT")
    		->join("cg_county xct","n.exec_county=xct.id","LEFT")
            ->join("pro_products pp","td.pinpai=pp.id","LEFT")
            ->join("cg_kaoheqi1 k1","k1.id=td.yijikaoheqi")
    		->where($where)
    		->limit($start,$list)
    		->field("td.id,ta.no,td.detail_no,cn.title as cainian,k1.title as yijikaoheqi,n.title as wangdian,pp.pro_name as pinpai,td.feiyong,n.mysn,n.sn,n.direct,ch1.title as channel1,ch2.title as channel2,xp.title as xingsheng,xc.title as xingshi,xct.title as xingc,z.title as zong,p.title as prov,c.title as shi,jxs.sn as jxssn,jxs.title as jxstitle,s.sn as ssn,s.title as stitle,ze.em_name as fenquhead,pe.em_name as shenghead,ce.em_name as shihead")
    		->select();
    		 $this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("data",$data);
		$this->assign("apply",$apply);

		return $this->fetch('allaptongji');
	}

	public function excel_duqu($fid,$state,$zhuti,$filename){
		//$title="AP费用预算格式";
		Vendor("PHPExcel");
        Vendor("PHPExcel.IOFactory");
        
       	Vendor("PHPExcel.Reader.Excel5");
       	//$filename=ROOT_PATH . 'public/a.xls';
       	$objReader = PHPExcel_IOFactory::createReaderForFile($filename);
		$objPHPExcel = $objReader->load($filename);
		$sheet=$objPHPExcel->setActiveSheetIndex(0);
		$highestRow = $sheet->getHighestRow(); // 取得总行数
		$highestColumm = $sheet->getHighestColumn(); // 取得总列数
		//获取基本格式
		//return 1;
		$target=Db::table("target_net_format")
        ->where(["id"=>$fid])->find();

        $datatianxie=[];
      $datatianxie[]="client_id";
      $client="网点";
       
        if($target["product_id"]){
        	$datatianxie[]="product_id";
        	$tmp=Db::table("field")->where(["id"=>$target["product_id"]])->find();
        	$product=$tmp["field_name"];
        }
        
       
        	
       
        if($target["kaoheqi"]){
        	
        	$datatianxie[]="kaoheqi";
        	$tmp=Db::table("field")->where(["id"=>$target["kaoheqi"]])->find();
        	$kaoheqi=$tmp["field_name"];
        }
        
       
        	 $datatianxie[]="yusuan";
       
       //return json_encode($datatianxie);
       // print_r($datatianxie);exit;
        //$
       $alldata=[];
       $timenow=date("Ymd");
       $num=Db::table("target_net_apply")->where("no like '$timenow%'")->count();
       $num++;
       $strnum=$timenow.sprintf("%05d", $num);
     
       	$datata=["type_id"=>$fid,"c_users_id"=>Session::get("login_id"),"created_on"=>date("Y-m-d H:i:s"),"state"=>1,"title"=>$zhuti,"path"=>$filename,"no"=>$strnum];
       	
       	$conunt_de=0;
        for ($row = 3; $row <= $highestRow; $row++){//行数是以第1行开始
        	if(!trim($sheet->getCell('A'.$row)->getValue())){
        		break;
        	}
        	$conunt_de++;
        	$strcountde=sprintf("%03d",$conunt_de);
        	 $adddata=["detail_no"=>$strcountde];
			for ($column = 0; $column < count($datatianxie); $column++) {//列数是以A列开始
				$c=chr($column+65);//echo $c;exit;
				$content=trim($sheet->getCell($c.$row)->getValue());
				switch ($datatianxie[$column]) {
					
					case 'client_id':
					#客户
						if($content){
							if($client=="经销商"){
								$tmpclient=Db::table("cg_jxs")->where(["title"=>$content,"isdel"=>0])->find();
								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["id"];
								}else{
									 
									return json_encode(["msg"=>"经销商不存在或已删除","state"=>2]);
								}
							}else if($client=="系统"){
								$tmpclient=Db::table("cg_system")->alias("s")
								->join("cg_jxs j","s.jxs=j.id")
								->where(["s.title"=>$content,"s.isdel"=>0,"j.isdel"=>0])
								->field("j.id as jxs,s.id as xitong")->find();
								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["jxs"];
									$adddata["xitong"]=$tmpclient["xitong"];
								}else{
									 
									return json_encode(["msg"=>"系统不存或所属经销商已删除","state"=>2]);
								}
							}else if($client=="网点"){
								$tmpclient=Db::table("cg_system")->alias("s")
								->join("cg_jxs j","s.jxs=j.id")
								->join("cg_net n","n.system_id=s.id")
								->where(["n.title"=>$content,"n.isdel"=>0,"j.isdel"=>0,"s.isdel"=>0])
								->field("j.id as jxs,s.id as xitong,n.id as net")->find();
								if($tmpclient){
									$adddata["jingxiao"]=$tmpclient["jxs"];
									$adddata["xitong"]=$tmpclient["xitong"];
									$adddata["wangdian"]=$tmpclient["net"];
								}else{
									 
									return json_encode(["msg"=>"网点不存或所属经销商，系统已删除","state"=>2]);
								}
							}else{
								//错误终止
								
								return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>$client."必须填写","state"=>2]);
						}
					break;
					case 'product_id':
					#产品纬度
						if($content){
							if($product=="品牌"){
								$tmppro=Db::table("pro_brands")->where(["is_actived"=>1,"is_deleted"=>0,"brand_name"=>$content])
								->field("id")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["id"];
								}else{
									
									return json_encode(["msg"=>"品牌不存或已删除","state"=>2]);
								}

							}else if($product=="品类"){
								$tmppro=Db::table("pro_category")->alias("c")
								->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"c.category_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0])
								->field("b.id as brand_name,c.id as category_name")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["brand_name"];
									$adddata["pinlei"]=$tmppro["category_name"];
								}else{
									
									return json_encode(["msg"=>"品类不存或所属品牌已删除","state"=>2]);
								}
							}else if($product=="系列"){
								$tmppro=Db::table("pro_series")->alias("s")
								->join("pro_category c","s.category_id=c.id")
								->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"s.series_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0,"s.is_actived"=>1,"s.is_deleted"=>0])
								->field("b.id as brand_name,c.id as category_name,s.id as series_name")->find();
								if($tmppro){
									$adddata["pinpai"]=$tmppro["brand_name"];
									$adddata["pinlei"]=$tmppro["category_name"];
									$adddata["xilie"]=$tmppro["series_name"];
								}else{
									
									return json_encode(["msg"=>"系列不存或所属品牌或品类已删除","state"=>2]);
								}
							}else{
								//错误终止
								// Db::rollBack();
								return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
							}
						}else{
							 
							return json_encode(["msg"=>$product."必须填写","state"=>2]);
						}
					break;
					
				
					case "kaoheqi":
					#考核期
					if($content){
						if($kaoheqi=="一级考核期"){
							$tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")
							->join("cg_cainian c","k1.cid=c.id")->where(["k1.title"=>$content,"c.id"=>$target["cainian"]])->field("k1.id as title1")->find();
							if($tmpkaohe){
								$adddata["yijikaoheqi"]=$tmpkaohe["title1"];
							}else{
								 
								return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
							}
						}else if($kaoheqi=="二级考核期"){
							$tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")
							->join("cg_cainian c","k1.cid=c.id")->where(["k2.title"=>$content,"c.id"=>$target["cainian"]])->field("k1.id as title1,k2.id as title2")->find();
							if($tmpkaohe){
								$adddata["yijikaoheqi"]=$tmpkaohe["title1"];
								$adddata["erjikaoheqi"]=$tmpkaohe["title2"];
							}else{
								
								return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
							}
						}else{
							
							return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
						}
					}else{
						
						return json_encode(["msg"=>$kaoheqi."必须填写","state"=>2]);
					}
					break;
					
					case 'yusuan':
					if($content){
						$adddata["feiyong"]=$content;
					}else{
						
						return json_encode(["msg"=>"预算必须填写","state"=>2]);
					}
					break;
					default:
						# code...
						break;
				}
				
				//$dataset[] = $sheet->getCell($c.$row)->getValue();
				//echo $c.$row.":".$sheet->getCell($c.$row)->getValue()."<br />";
			}
			$alldata[]=$adddata;
		}
		  Db::startTrans();
       try{
			$res2=Db::table("target_net_apply")->insertGetId($datata);
			for($i=0;$i<count($alldata);$i++){
				$alldata[$i]["apply_id"]=$res2;
			}
			$res1=Db::table("target_net_detail")->insertAll($alldata);
		}catch(\Exception $e){

                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
            if($res1&&$res2){
                Db::commit(); //事物提交
                if($state==3){
                    $this->gowork(25,$res2);
                }
               return json_encode(["msg"=>"操作已成功","state"=>1]);
            }else{
                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
		//print_r($alldata);
	}
    public function detail($tid){

        $apply=Db::table("target_net_apply")->alias("t")
        ->join("target_net_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_net_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.tag,t.state2")
        ->find();
        $this->assign("apply",$apply);
         $model=db("target_net_detail")->where(["apply_id"=>$tid])->find();
        $this->assign("model",$model);
          //明细
        $detail=Db::table("target_net_detail")->alias("t")
        
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")->join("cg_cainian cn","k1.cid=cn.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
            //获取财年和考核期
        $ck=Db::table("target_net_detail")->query("select c.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,c.id as cid,k1.id as k1id,k2.id as k2id  from target_net_detail as t  inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id LEFT join cg_kaoheqi2 as k2 on t.erjikaoheqi=k2.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid group by c.id,t.yijikaoheqi,t.erjikaoheqi,c.id,k1.id,k2.id ");
        for($i=0;$i<count($ck);$i++){
            if($ck[$i]["erjikaoheqi"]){
                $ck[$i]["kaoheqi"]=$ck[$i]["erjikaoheqi"];
                $ck[$i]["kid"]=$ck[$i]["k2id"];
            }else{
                $ck[$i]["kaoheqi"]=$ck[$i]["yijikaoheqi"]; $ck[$i]["kid"]=$ck[$i]["k1id"];
            }
        }
        $this->assign("ck",$ck);
        //考核期数量
        $kc=count($ck)+1;
    
        $this->assign("kc",$kc);
        //产品纬度
        if($model["xilie"]){
            $grouppro="t.xilie";
            $strpro="t.pinpai,t.pinlei,t.xilie";
        }else if($model["pinlei"]){
            $grouppro="t.pinlei";
            $strpro="t.pinpai,t.pinlei";
        }else{
            $grouppro="t.pinpai";
            
            $strpro="t.pinpai";
        }
        if($model["erjikaoheqi"]){
            $prode=Db::table("target_net_detail")->query("select c.id as cainian,erjikaoheqi as yijikaoheqi,{$strpro},sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid and t.pinpai is not null group by {$grouppro},t.erjikaoheqi");
        }else{
            $prode=Db::table("target_net_detail")->query("select c.id as cainian,yijikaoheqi,{$strpro},sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id  where t.apply_id=$tid and t.pinpai is not null group by {$grouppro},t.yijikaoheqi");
        }   
        
        foreach ($prode as $k => $v) {
            # code...
            if($v["pinpai"])
                $prode[$k]["brand_name"]=db("pro_brands")->where(["id"=>$v["pinpai"]])->value("brand_name");
            else
                $prode[$k]["brand_name"]="";
            if(isset($v["pinlei"])){
                $prode[$k]["category_name"]=db("pro_category")->where(["id"=>$v["pinlei"]])->value("category_name");
            }
            if(isset($v["xilie"])){
                $prode[$k]["series_name"]=db("pro_series")->where(["id"=>$v["xilie"]])->value("series_name");
            }
        }
        $pro=Db::table("target_net_detail")->query("select {$strpro},sum(feiyong) as f  from target_net_detail as t  where apply_id=$tid and pinpai is not null group by {$grouppro}");
        foreach ($pro as $k => $v) {
            # code...
            if($v["pinpai"])
                $pro[$k]["brand_name"]=db("pro_brands")->where(["id"=>$v["pinpai"]])->value("brand_name");
            else
                $pro[$k]["brand_name"]="";
            if(isset($v["pinlei"])){
                $pro[$k]["category_name"]=db("pro_category")->where(["id"=>$v["pinlei"]])->value("category_name");
            }
            if(isset($v["xilie"])){
                $pro[$k]["series_name"]=db("pro_series")->where(["id"=>$v["xilie"]])->value("series_name");
            }
        }
       // print_r($pro);
        $sumpro=0;
        for($i=0;$i<count($pro);$i++){
            $sumpro+=$pro[$i]["f"];
        }
        $this->assign("sumpro",$sumpro);
        $this->assign("pro",$pro);
        $this->assign("prode",$prode);
        //区域客户
        $client=Db::table("target_net_detail")->query("select t.wangdian,j.title as jxs,s.title as sys,c.title as net,sum(feiyong) as f  from target_net_detail as t inner join cg_jxs as j on t.jingxiao=j.id inner join cg_system as s on t.xitong=s.id inner join cg_net as c on t.wangdian=c.id where t.apply_id=$tid and t.jingxiao is not null group by t.wangdian");
        if($model["erjikaoheqi"]){
            $clientde=Db::table("target_net_detail")->query("select c.id as cainian,erjikaoheqi as yijikaoheqi,wangdian,sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid and t.jingxiao is not null group by wangdian,t.erjikaoheqi");
        }else{
            $clientde=Db::table("target_net_detail")->query("select c.id as cainian,yijikaoheqi,wangdian,sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id  where t.apply_id=$tid and t.jingxiao is not null group by wangdian,t.yijikaoheqi");
        }   
       
        $sumclient=0;
        for($i=0;$i<count($clientde);$i++){
            $sumclient+=$clientde[$i]["f"];
           
        }
        $this->assign("client",$client);
        $this->assign("clientde",$clientde);
        $this->assign("sumclient",$sumclient);
        if($apply["state2"]==1)
            $logs=$this->getlog(25,$tid);
        else
             $logs=$this->getlog(26,$tid);
         $this->assign("logs",$logs);
         if($apply["state2"]==2){
             $yuandetail=Db::table("target_net_detail")->alias("t")
              ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
            ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
            ->join("cg_cainian cn","k1.cid=cn.id","LEFT")
           
            ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
            ->join("pro_category pc","t.pinlei=pc.id","LEFT")
            ->join("pro_series ps","t.xilie=ps.id","LEFT")
            ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
            ->join("cg_system s","t.xitong=s.id","LEFT")
            ->join("cg_net n","t.wangdian=n.id","LEFT")
          
            ->where(["t.apply_id"=>$apply["yuanid"]])
            ->field("s.title as xitong,n.title as net,j.title as jingxiao,n.title as wangdian,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie")
            ->select();
            $tongjidata=[];
            for($i=0;$i<count($detail);$i++){
                $tongji=$detail[$i];
                $tongji["yuanfeiyong"]=0;
                
                for($j=0;$j<count($yuandetail);$j++){
                    if($detail[$i]["xitong"]==$yuandetail[$j]["xitong"]&&$detail[$i]["wangdian"]==$yuandetail[$j]["wangdian"]&&$detail[$i]["yijikaoheqi"]==$yuandetail[$j]["yijikaoheqi"]&&$detail[$i]["erjikaoheqi"]==$yuandetail[$j]["erjikaoheqi"]&&$detail[$i]["cainian"]==$yuandetail[$j]["cainian"]&&$detail[$i]["jingxiao"]==$yuandetail[$j]["jingxiao"]&&$detail[$i]["pinpai"]==$yuandetail[$j]["pinpai"]&&$detail[$i]["pinlei"]==$yuandetail[$j]["pinlei"]&&$detail[$i]["xilie"]==$yuandetail[$j]["xilie"]){
                        //内容相似
                        $tongji["yuanfeiyong"]=$yuandetail[$j]["feiyong"];
                        array_splice($yuandetail, $j, 1);break;
                    }
                }
                $tongji["duibi"]=$tongji["feiyong"]-$tongji["yuanfeiyong"];
                $tongjidata[]=$tongji;
            }
            for($i=0;$i<count($yuandetail);$i++){
                $tongji=["xitong"=>$yuandetail[$i]["xitong"],"wangdian"=>$yuandetail[$i]["wangdian"],"cainian"=>$yuandetail[$i]["cainian"],"yijikaoheqi"=>$yuandetail[$i]["yijikaoheqi"],"erjikaoheqi"=>$yuandetail[$i]["erjikaoheqi"],"feiyong"=>0,"yuanfeiyong"=>$yuandetail[$i]["feiyong"],"duibi"=>0-$yuandetail[$i]["feiyong"],"jingxiao"=>$yuandetail[$i]["jingxiao"],"pinpai"=>$yuandetail[$i]["pinpai"],"pinlei"=>$yuandetail[$i]["pinlei"],"xilie"=>$yuandetail[$i]["xilie"]];
                $tongjidata[]=$tongji;
            }
            $this->assign("tongjidata",$tongjidata);
        }
        return $this->fetch();
    }
    public function yidong($tid){
        
        $apply=Db::table("target_net_apply")->alias("t")
        ->join("target_net_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_net_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.tag,t.state2")
        ->find();
        $this->assign("apply",$apply);
         $model=db("target_net_detail")->where(["apply_id"=>$tid])->find();
        $this->assign("model",$model);
          //明细
        $detail=Db::table("target_net_detail")->alias("t")
        
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")->join("cg_cainian cn","k1.cid=cn.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
            //获取财年和考核期
        $ck=Db::table("target_net_detail")->query("select c.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,c.id as cid,k1.id as k1id,k2.id as k2id  from target_net_detail as t  inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id LEFT join cg_kaoheqi2 as k2 on t.erjikaoheqi=k2.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid group by c.id,t.yijikaoheqi,t.erjikaoheqi,c.id,k1.id,k2.id ");
        for($i=0;$i<count($ck);$i++){
            if($ck[$i]["erjikaoheqi"]){
                $ck[$i]["kaoheqi"]=$ck[$i]["erjikaoheqi"];
                $ck[$i]["kid"]=$ck[$i]["k2id"];
            }else{
                $ck[$i]["kaoheqi"]=$ck[$i]["yijikaoheqi"]; $ck[$i]["kid"]=$ck[$i]["k1id"];
            }
        }
        $this->assign("ck",$ck);
        //考核期数量
        $kc=count($ck)+1;
    
        $this->assign("kc",$kc);
        //产品纬度
        if($model["xilie"]){
            $grouppro="t.xilie";
            $strpro="t.pinpai,t.pinlei,t.xilie";
        }else if($model["pinlei"]){
            $grouppro="t.pinlei";
            $strpro="t.pinpai,t.pinlei";
        }else{
            $grouppro="t.pinpai";
            
            $strpro="t.pinpai";
        }
        if($model["erjikaoheqi"]){
            $prode=Db::table("target_net_detail")->query("select c.id as cainian,erjikaoheqi as yijikaoheqi,{$strpro},sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid and t.pinpai is not null group by {$grouppro},t.erjikaoheqi");
        }else{
            $prode=Db::table("target_net_detail")->query("select c.id as cainian,yijikaoheqi,{$strpro},sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id  where t.apply_id=$tid and t.pinpai is not null group by {$grouppro},t.yijikaoheqi");
        }   
        
        foreach ($prode as $k => $v) {
            # code...
            if($v["pinpai"])
                $prode[$k]["brand_name"]=db("pro_brands")->where(["id"=>$v["pinpai"]])->value("brand_name");
            else
                $prode[$k]["brand_name"]="";
            if(isset($v["pinlei"])){
                $prode[$k]["category_name"]=db("pro_category")->where(["id"=>$v["pinlei"]])->value("category_name");
            }
            if(isset($v["xilie"])){
                $prode[$k]["series_name"]=db("pro_series")->where(["id"=>$v["xilie"]])->value("series_name");
            }
        }
        $pro=Db::table("target_net_detail")->query("select {$strpro},sum(feiyong) as f  from target_net_detail as t  where apply_id=$tid and pinpai is not null group by {$grouppro}");
        foreach ($pro as $k => $v) {
            # code...
            if($v["pinpai"])
                $pro[$k]["brand_name"]=db("pro_brands")->where(["id"=>$v["pinpai"]])->value("brand_name");
            else
                $pro[$k]["brand_name"]="";
            if(isset($v["pinlei"])){
                $pro[$k]["category_name"]=db("pro_category")->where(["id"=>$v["pinlei"]])->value("category_name");
            }
            if(isset($v["xilie"])){
                $pro[$k]["series_name"]=db("pro_series")->where(["id"=>$v["xilie"]])->value("series_name");
            }
        }
       // print_r($pro);
        $sumpro=0;
        for($i=0;$i<count($pro);$i++){
            $sumpro+=$pro[$i]["f"];
        }
        $this->assign("sumpro",$sumpro);
        $this->assign("pro",$pro);
        $this->assign("prode",$prode);
        //区域客户
        $client=Db::table("target_net_detail")->query("select t.wangdian,j.title as jxs,s.title as sys,c.title as net,sum(feiyong) as f  from target_net_detail as t inner join cg_jxs as j on t.jingxiao=j.id inner join cg_system as s on t.xitong=s.id inner join cg_net as c on t.wangdian=c.id where t.apply_id=$tid and t.jingxiao is not null group by t.wangdian");
        if($model["erjikaoheqi"]){
            $clientde=Db::table("target_net_detail")->query("select c.id as cainian,erjikaoheqi as yijikaoheqi,wangdian,sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid and t.jingxiao is not null group by wangdian,t.erjikaoheqi");
        }else{
            $clientde=Db::table("target_net_detail")->query("select c.id as cainian,yijikaoheqi,wangdian,sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id  where t.apply_id=$tid and t.jingxiao is not null group by wangdian,t.yijikaoheqi");
        }   
       
        $sumclient=0;
        for($i=0;$i<count($clientde);$i++){
            $sumclient+=$clientde[$i]["f"];
           
        }
        $this->assign("client",$client);
        $this->assign("clientde",$clientde);
        $this->assign("sumclient",$sumclient);
        if($apply["state2"]==1)
            $logs=$this->getlog(25,$tid);
        else
             $logs=$this->getlog(26,$tid);
         $this->assign("logs",$logs);
         if($apply["state2"]==2){
             $yuandetail=Db::table("target_net_detail")->alias("t")
              ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
            ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
            ->join("cg_cainian cn","k1.cid=cn.id","LEFT")
           
            ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
            ->join("pro_category pc","t.pinlei=pc.id","LEFT")
            ->join("pro_series ps","t.xilie=ps.id","LEFT")
            ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
            ->join("cg_system s","t.xitong=s.id","LEFT")
            ->join("cg_net n","t.wangdian=n.id","LEFT")
          
            ->where(["t.apply_id"=>$apply["yuanid"]])
            ->field("s.title as xitong,n.title as net,j.title as jingxiao,n.title as wangdian,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie")
            ->select();
            $tongjidata=[];
            for($i=0;$i<count($detail);$i++){
                $tongji=$detail[$i];
                $tongji["yuanfeiyong"]=0;
                
                for($j=0;$j<count($yuandetail);$j++){
                    if($detail[$i]["xitong"]==$yuandetail[$j]["xitong"]&&$detail[$i]["wangdian"]==$yuandetail[$j]["wangdian"]&&$detail[$i]["yijikaoheqi"]==$yuandetail[$j]["yijikaoheqi"]&&$detail[$i]["erjikaoheqi"]==$yuandetail[$j]["erjikaoheqi"]&&$detail[$i]["cainian"]==$yuandetail[$j]["cainian"]&&$detail[$i]["jingxiao"]==$yuandetail[$j]["jingxiao"]&&$detail[$i]["pinpai"]==$yuandetail[$j]["pinpai"]&&$detail[$i]["pinlei"]==$yuandetail[$j]["pinlei"]&&$detail[$i]["xilie"]==$yuandetail[$j]["xilie"]){
                        //内容相似
                        $tongji["yuanfeiyong"]=$yuandetail[$j]["feiyong"];
                        array_splice($yuandetail, $j, 1);break;
                    }
                }
                $tongji["duibi"]=$tongji["feiyong"]-$tongji["yuanfeiyong"];
                $tongjidata[]=$tongji;
            }
            for($i=0;$i<count($yuandetail);$i++){
                $tongji=["xitong"=>$yuandetail[$i]["xitong"],"wangdian"=>$yuandetail[$i]["wangdian"],"cainian"=>$yuandetail[$i]["cainian"],"yijikaoheqi"=>$yuandetail[$i]["yijikaoheqi"],"erjikaoheqi"=>$yuandetail[$i]["erjikaoheqi"],"feiyong"=>0,"yuanfeiyong"=>$yuandetail[$i]["feiyong"],"duibi"=>0-$yuandetail[$i]["feiyong"],"jingxiao"=>$yuandetail[$i]["jingxiao"],"pinpai"=>$yuandetail[$i]["pinpai"],"pinlei"=>$yuandetail[$i]["pinlei"],"xilie"=>$yuandetail[$i]["xilie"]];
                $tongjidata[]=$tongji;
            }
            $this->assign("tongjidata",$tongjidata);
        }
        return $this->fetch();
    }
    public function shenhe($myId,$trackId,$workId){
         $workfloawde=Db::table("cg_workflow")->where(["id"=>$myId])->find();
        $tid=$workfloawde["yw_id"];
        $apply=Db::table("target_net_apply")->alias("t")
        ->join("target_net_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_net_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.tag,t.state2")
        ->find();
        if($apply["state"]==2){
            $this->redirect('Wangdian/zuofei', ['tid' => $tid,'from'=>2]);
            return;
        }
        $this->assign("apply",$apply);
         $model=db("target_net_detail")->where(["apply_id"=>$tid])->find();
        $this->assign("model",$model);
          //明细
        $detail=Db::table("target_net_detail")->alias("t")
        
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")->join("cg_cainian cn","k1.cid=cn.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
            //获取财年和考核期
        $ck=Db::table("target_net_detail")->query("select c.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,c.id as cid,k1.id as k1id,k2.id as k2id  from target_net_detail as t  inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id LEFT join cg_kaoheqi2 as k2 on t.erjikaoheqi=k2.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid group by c.id,t.yijikaoheqi,t.erjikaoheqi,c.id,k1.id,k2.id ");
        for($i=0;$i<count($ck);$i++){
            if($ck[$i]["erjikaoheqi"]){
                $ck[$i]["kaoheqi"]=$ck[$i]["erjikaoheqi"];
                $ck[$i]["kid"]=$ck[$i]["k2id"];
            }else{
                $ck[$i]["kaoheqi"]=$ck[$i]["yijikaoheqi"]; $ck[$i]["kid"]=$ck[$i]["k1id"];
            }
        }
        $this->assign("ck",$ck);
        //考核期数量
        $kc=count($ck)+1;
    
        $this->assign("kc",$kc);
        //产品纬度
        if($model["xilie"]){
            $grouppro="t.xilie";
            $strpro="t.pinpai,t.pinlei,t.xilie";
        }else if($model["pinlei"]){
            $grouppro="t.pinlei";
            $strpro="t.pinpai,t.pinlei";
        }else{
            $grouppro="t.pinpai";
            
            $strpro="t.pinpai";
        }
        if($model["erjikaoheqi"]){
            $prode=Db::table("target_net_detail")->query("select c.id as cainian,erjikaoheqi as yijikaoheqi,{$strpro},sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid and t.pinpai is not null group by {$grouppro},t.erjikaoheqi");
        }else{
            $prode=Db::table("target_net_detail")->query("select c.id as cainian,yijikaoheqi,{$strpro},sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id  where t.apply_id=$tid and t.pinpai is not null group by {$grouppro},t.yijikaoheqi");
        }   
        
        foreach ($prode as $k => $v) {
            # code...
            if($v["pinpai"])
                $prode[$k]["brand_name"]=db("pro_brands")->where(["id"=>$v["pinpai"]])->value("brand_name");
            else
                $prode[$k]["brand_name"]="";
            if(isset($v["pinlei"])){
                $prode[$k]["category_name"]=db("pro_category")->where(["id"=>$v["pinlei"]])->value("category_name");
            }
            if(isset($v["xilie"])){
                $prode[$k]["series_name"]=db("pro_series")->where(["id"=>$v["xilie"]])->value("series_name");
            }
        }
        $pro=Db::table("target_net_detail")->query("select {$strpro},sum(feiyong) as f  from target_net_detail as t  where apply_id=$tid and pinpai is not null group by {$grouppro}");
        foreach ($pro as $k => $v) {
            # code...
            if($v["pinpai"])
                $pro[$k]["brand_name"]=db("pro_brands")->where(["id"=>$v["pinpai"]])->value("brand_name");
            else
                $pro[$k]["brand_name"]="";
            if(isset($v["pinlei"])){
                $pro[$k]["category_name"]=db("pro_category")->where(["id"=>$v["pinlei"]])->value("category_name");
            }
            if(isset($v["xilie"])){
                $pro[$k]["series_name"]=db("pro_series")->where(["id"=>$v["xilie"]])->value("series_name");
            }
        }
       // print_r($pro);
        $sumpro=0;
        for($i=0;$i<count($pro);$i++){
            $sumpro+=$pro[$i]["f"];
        }
        $this->assign("sumpro",$sumpro);
        $this->assign("pro",$pro);
        $this->assign("prode",$prode);
        //区域客户
        $client=Db::table("target_net_detail")->query("select t.wangdian,j.title as jxs,s.title as sys,c.title as net,sum(feiyong) as f  from target_net_detail as t inner join cg_jxs as j on t.jingxiao=j.id inner join cg_system as s on t.xitong=s.id inner join cg_net as c on t.wangdian=c.id where t.apply_id=$tid and t.jingxiao is not null group by t.wangdian");
        if($model["erjikaoheqi"]){
            $clientde=Db::table("target_net_detail")->query("select c.id as cainian,erjikaoheqi as yijikaoheqi,wangdian,sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid and t.jingxiao is not null group by wangdian,t.erjikaoheqi");
        }else{
            $clientde=Db::table("target_net_detail")->query("select c.id as cainian,yijikaoheqi,wangdian,sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id  where t.apply_id=$tid and t.jingxiao is not null group by wangdian,t.yijikaoheqi");
        }   
       
        $sumclient=0;
        for($i=0;$i<count($clientde);$i++){
            $sumclient+=$clientde[$i]["f"];
           
        }
        $this->assign("client",$client);
        $this->assign("clientde",$clientde);
        $this->assign("sumclient",$sumclient);
        if($apply["state2"]==1)
            $logs=$this->getlog(25,$tid);
        else
             $logs=$this->getlog(26,$tid);
         $this->assign("logs",$logs);
         if($apply["state2"]==2){
             $yuandetail=Db::table("target_net_detail")->alias("t")
              ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
            ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
            ->join("cg_cainian cn","k1.cid=cn.id","LEFT")
           
            ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
            ->join("pro_category pc","t.pinlei=pc.id","LEFT")
            ->join("pro_series ps","t.xilie=ps.id","LEFT")
            ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
            ->join("cg_system s","t.xitong=s.id","LEFT")
            ->join("cg_net n","t.wangdian=n.id","LEFT")
          
            ->where(["t.apply_id"=>$apply["yuanid"]])
            ->field("s.title as xitong,n.title as net,j.title as jingxiao,n.title as wangdian,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie")
            ->select();
            $tongjidata=[];
            for($i=0;$i<count($detail);$i++){
                $tongji=$detail[$i];
                $tongji["yuanfeiyong"]=0;
                
                for($j=0;$j<count($yuandetail);$j++){
                    if($detail[$i]["xitong"]==$yuandetail[$j]["xitong"]&&$detail[$i]["wangdian"]==$yuandetail[$j]["wangdian"]&&$detail[$i]["yijikaoheqi"]==$yuandetail[$j]["yijikaoheqi"]&&$detail[$i]["erjikaoheqi"]==$yuandetail[$j]["erjikaoheqi"]&&$detail[$i]["cainian"]==$yuandetail[$j]["cainian"]&&$detail[$i]["jingxiao"]==$yuandetail[$j]["jingxiao"]&&$detail[$i]["pinpai"]==$yuandetail[$j]["pinpai"]&&$detail[$i]["pinlei"]==$yuandetail[$j]["pinlei"]&&$detail[$i]["xilie"]==$yuandetail[$j]["xilie"]){
                        //内容相似
                        $tongji["yuanfeiyong"]=$yuandetail[$j]["feiyong"];
                        array_splice($yuandetail, $j, 1);break;
                    }
                }
                $tongji["duibi"]=$tongji["feiyong"]-$tongji["yuanfeiyong"];
                $tongjidata[]=$tongji;
            }
            for($i=0;$i<count($yuandetail);$i++){
                $tongji=["xitong"=>$yuandetail[$i]["xitong"],"wangdian"=>$yuandetail[$i]["wangdian"],"cainian"=>$yuandetail[$i]["cainian"],"yijikaoheqi"=>$yuandetail[$i]["yijikaoheqi"],"erjikaoheqi"=>$yuandetail[$i]["erjikaoheqi"],"feiyong"=>0,"yuanfeiyong"=>$yuandetail[$i]["feiyong"],"duibi"=>0-$yuandetail[$i]["feiyong"],"jingxiao"=>$yuandetail[$i]["jingxiao"],"pinpai"=>$yuandetail[$i]["pinpai"],"pinlei"=>$yuandetail[$i]["pinlei"],"xilie"=>$yuandetail[$i]["xilie"]];
                $tongjidata[]=$tongji;
            }
            $this->assign("tongjidata",$tongjidata);
        }
        return $this->fetch();
    }
    public function zuofeichuli(){
        $request = Request::instance();$data = $request->post();
      //print_r($data);exit;
        $aid=$data["aid"];
        $danju=db("target_net_apply")->where(["id"=>$aid])->find();
        if($danju["state2"]==1)
            $fid=25;
        else
            $fid=26;
        $res=$this->goworker($fid,$aid,$data["nodeKey"],$data["selectId"],$data["selectName"]);
        if($res["operateStatus"]=="success"){
            
            try{
                if($danju["state2"]==2){
                //异动
                Db::table("target_net_apply")->where(["id"=>$danju["yuanid"]])->update(["tag"=>0,"nextid"=>null,"islock"=>0]);
                } 
                $res=Db::table("target_net_apply")->where(["id"=>$aid])->update(["state"=>8]);
            }catch (\Exception $e){
            
              return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
          return json_encode(["msg"=>"","state"=>1]);
            
        }else{
            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
        }
    }
    public function zuofei($tid,$from=0){
        $this->assign("from",$from);
      
        $apply=Db::table("target_net_apply")->alias("t")
        ->join("target_net_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_net_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.tag,t.state2")
        ->find();
       
        $this->assign("apply",$apply);
         $model=db("target_net_detail")->where(["apply_id"=>$tid])->find();
        $this->assign("model",$model);
          //明细
        $detail=Db::table("target_net_detail")->alias("t")
        
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")->join("cg_cainian cn","k1.cid=cn.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
            //获取财年和考核期
        $ck=Db::table("target_net_detail")->query("select c.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,c.id as cid,k1.id as k1id,k2.id as k2id  from target_net_detail as t  inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id LEFT join cg_kaoheqi2 as k2 on t.erjikaoheqi=k2.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid group by c.id,t.yijikaoheqi,t.erjikaoheqi,c.id,k1.id,k2.id ");
        for($i=0;$i<count($ck);$i++){
            if($ck[$i]["erjikaoheqi"]){
                $ck[$i]["kaoheqi"]=$ck[$i]["erjikaoheqi"];
                $ck[$i]["kid"]=$ck[$i]["k2id"];
            }else{
                $ck[$i]["kaoheqi"]=$ck[$i]["yijikaoheqi"]; $ck[$i]["kid"]=$ck[$i]["k1id"];
            }
        }
        $this->assign("ck",$ck);
        //考核期数量
        $kc=count($ck)+1;
    
        $this->assign("kc",$kc);
        //产品纬度
        if($model["xilie"]){
            $grouppro="t.xilie";
            $strpro="t.pinpai,t.pinlei,t.xilie";
        }else if($model["pinlei"]){
            $grouppro="t.pinlei";
            $strpro="t.pinpai,t.pinlei";
        }else{
            $grouppro="t.pinpai";
            
            $strpro="t.pinpai";
        }
        if($model["erjikaoheqi"]){
            $prode=Db::table("target_net_detail")->query("select c.id as cainian,erjikaoheqi as yijikaoheqi,{$strpro},sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid and t.pinpai is not null group by {$grouppro},t.erjikaoheqi");
        }else{
            $prode=Db::table("target_net_detail")->query("select c.id as cainian,yijikaoheqi,{$strpro},sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id  where t.apply_id=$tid and t.pinpai is not null group by {$grouppro},t.yijikaoheqi");
        }   
        
        foreach ($prode as $k => $v) {
            # code...
            if($v["pinpai"])
                $prode[$k]["brand_name"]=db("pro_brands")->where(["id"=>$v["pinpai"]])->value("brand_name");
            else
                $prode[$k]["brand_name"]="";
            if(isset($v["pinlei"])){
                $prode[$k]["category_name"]=db("pro_category")->where(["id"=>$v["pinlei"]])->value("category_name");
            }
            if(isset($v["xilie"])){
                $prode[$k]["series_name"]=db("pro_series")->where(["id"=>$v["xilie"]])->value("series_name");
            }
        }
        $pro=Db::table("target_net_detail")->query("select {$strpro},sum(feiyong) as f  from target_net_detail as t  where apply_id=$tid and pinpai is not null group by {$grouppro}");
        foreach ($pro as $k => $v) {
            # code...
            if($v["pinpai"])
                $pro[$k]["brand_name"]=db("pro_brands")->where(["id"=>$v["pinpai"]])->value("brand_name");
            else
                $pro[$k]["brand_name"]="";
            if(isset($v["pinlei"])){
                $pro[$k]["category_name"]=db("pro_category")->where(["id"=>$v["pinlei"]])->value("category_name");
            }
            if(isset($v["xilie"])){
                $pro[$k]["series_name"]=db("pro_series")->where(["id"=>$v["xilie"]])->value("series_name");
            }
        }
       // print_r($pro);
        $sumpro=0;
        for($i=0;$i<count($pro);$i++){
            $sumpro+=$pro[$i]["f"];
        }
        $this->assign("sumpro",$sumpro);
        $this->assign("pro",$pro);
        $this->assign("prode",$prode);
        //区域客户
        $client=Db::table("target_net_detail")->query("select t.wangdian,j.title as jxs,s.title as sys,c.title as net,sum(feiyong) as f  from target_net_detail as t inner join cg_jxs as j on t.jingxiao=j.id inner join cg_system as s on t.xitong=s.id inner join cg_net as c on t.wangdian=c.id where t.apply_id=$tid and t.jingxiao is not null group by t.wangdian");
        if($model["erjikaoheqi"]){
            $clientde=Db::table("target_net_detail")->query("select c.id as cainian,erjikaoheqi as yijikaoheqi,wangdian,sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid and t.jingxiao is not null group by wangdian,t.erjikaoheqi");
        }else{
            $clientde=Db::table("target_net_detail")->query("select c.id as cainian,yijikaoheqi,wangdian,sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id  where t.apply_id=$tid and t.jingxiao is not null group by wangdian,t.yijikaoheqi");
        }   
       
        $sumclient=0;
        for($i=0;$i<count($clientde);$i++){
            $sumclient+=$clientde[$i]["f"];
           
        }
        $this->assign("client",$client);
        $this->assign("clientde",$clientde);
        $this->assign("sumclient",$sumclient);
        if($apply["state2"]==1)
            $logs=$this->getlog(25,$tid);
        else
             $logs=$this->getlog(26,$tid);
         $this->assign("logs",$logs);
         if($apply["state2"]==1)
            $btn=$this->getbton(25,$tid);
        else
            $btn=$this->getbton(26,$tid);
        $this->assign("btn",$btn);
         if($apply["state2"]==2){
             $yuandetail=Db::table("target_net_detail")->alias("t")
              ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
            ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
            ->join("cg_cainian cn","k1.cid=cn.id","LEFT")
           
            ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
            ->join("pro_category pc","t.pinlei=pc.id","LEFT")
            ->join("pro_series ps","t.xilie=ps.id","LEFT")
            ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
            ->join("cg_system s","t.xitong=s.id","LEFT")
            ->join("cg_net n","t.wangdian=n.id","LEFT")
          
            ->where(["t.apply_id"=>$apply["yuanid"]])
            ->field("s.title as xitong,n.title as net,j.title as jingxiao,n.title as wangdian,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie")
            ->select();
            $tongjidata=[];
            for($i=0;$i<count($detail);$i++){
                $tongji=$detail[$i];
                $tongji["yuanfeiyong"]=0;
                
                for($j=0;$j<count($yuandetail);$j++){
                    if($detail[$i]["xitong"]==$yuandetail[$j]["xitong"]&&$detail[$i]["wangdian"]==$yuandetail[$j]["wangdian"]&&$detail[$i]["yijikaoheqi"]==$yuandetail[$j]["yijikaoheqi"]&&$detail[$i]["erjikaoheqi"]==$yuandetail[$j]["erjikaoheqi"]&&$detail[$i]["cainian"]==$yuandetail[$j]["cainian"]&&$detail[$i]["jingxiao"]==$yuandetail[$j]["jingxiao"]&&$detail[$i]["pinpai"]==$yuandetail[$j]["pinpai"]&&$detail[$i]["pinlei"]==$yuandetail[$j]["pinlei"]&&$detail[$i]["xilie"]==$yuandetail[$j]["xilie"]){
                        //内容相似
                        $tongji["yuanfeiyong"]=$yuandetail[$j]["feiyong"];
                        array_splice($yuandetail, $j, 1);break;
                    }
                }
                $tongji["duibi"]=$tongji["feiyong"]-$tongji["yuanfeiyong"];
                $tongjidata[]=$tongji;
            }
            for($i=0;$i<count($yuandetail);$i++){
                $tongji=["xitong"=>$yuandetail[$i]["xitong"],"wangdian"=>$yuandetail[$i]["wangdian"],"cainian"=>$yuandetail[$i]["cainian"],"yijikaoheqi"=>$yuandetail[$i]["yijikaoheqi"],"erjikaoheqi"=>$yuandetail[$i]["erjikaoheqi"],"feiyong"=>0,"yuanfeiyong"=>$yuandetail[$i]["feiyong"],"duibi"=>0-$yuandetail[$i]["feiyong"],"jingxiao"=>$yuandetail[$i]["jingxiao"],"pinpai"=>$yuandetail[$i]["pinpai"],"pinlei"=>$yuandetail[$i]["pinlei"],"xilie"=>$yuandetail[$i]["xilie"]];
                $tongjidata[]=$tongji;
            }
            $this->assign("tongjidata",$tongjidata);
        }
        return $this->fetch();
    }
    public function xiugaide($tid){
        $apply=Db::table("target_net_apply")->alias("t")
        ->join("target_net_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_net_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.tag,t.state2")
        ->find();
        $this->assign("apply",$apply);
         $model=db("target_net_detail")->where(["apply_id"=>$tid])->find();
        $this->assign("model",$model);
          //明细
        $detail=Db::table("target_net_detail")->alias("t")
        
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")->join("cg_cainian cn","k1.cid=cn.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
            //获取财年和考核期
        $ck=Db::table("target_net_detail")->query("select c.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,c.id as cid,k1.id as k1id,k2.id as k2id  from target_net_detail as t  inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id LEFT join cg_kaoheqi2 as k2 on t.erjikaoheqi=k2.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid group by c.id,t.yijikaoheqi,t.erjikaoheqi,c.id,k1.id,k2.id ");
        for($i=0;$i<count($ck);$i++){
            if($ck[$i]["erjikaoheqi"]){
                $ck[$i]["kaoheqi"]=$ck[$i]["erjikaoheqi"];
                $ck[$i]["kid"]=$ck[$i]["k2id"];
            }else{
                $ck[$i]["kaoheqi"]=$ck[$i]["yijikaoheqi"]; $ck[$i]["kid"]=$ck[$i]["k1id"];
            }
        }
        $this->assign("ck",$ck);
        //考核期数量
        $kc=count($ck)+1;
    
        $this->assign("kc",$kc);
        //产品纬度
        if($model["xilie"]){
            $grouppro="t.xilie";
            $strpro="t.pinpai,t.pinlei,t.xilie";
        }else if($model["pinlei"]){
            $grouppro="t.pinlei";
            $strpro="t.pinpai,t.pinlei";
        }else{
            $grouppro="t.pinpai";
            
            $strpro="t.pinpai";
        }
        if($model["erjikaoheqi"]){
            $prode=Db::table("target_net_detail")->query("select c.id as cainian,erjikaoheqi as yijikaoheqi,{$strpro},sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid and t.pinpai is not null group by {$grouppro},t.erjikaoheqi");
        }else{
            $prode=Db::table("target_net_detail")->query("select c.id as cainian,yijikaoheqi,{$strpro},sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id  where t.apply_id=$tid and t.pinpai is not null group by {$grouppro},t.yijikaoheqi");
        }   
        
        foreach ($prode as $k => $v) {
            # code...
            if($v["pinpai"])
                $prode[$k]["brand_name"]=db("pro_brands")->where(["id"=>$v["pinpai"]])->value("brand_name");
            else
                $prode[$k]["brand_name"]="";
            if(isset($v["pinlei"])){
                $prode[$k]["category_name"]=db("pro_category")->where(["id"=>$v["pinlei"]])->value("category_name");
            }
            if(isset($v["xilie"])){
                $prode[$k]["series_name"]=db("pro_series")->where(["id"=>$v["xilie"]])->value("series_name");
            }
        }
        $pro=Db::table("target_net_detail")->query("select {$strpro},sum(feiyong) as f  from target_net_detail as t  where apply_id=$tid and pinpai is not null group by {$grouppro}");
        foreach ($pro as $k => $v) {
            # code...
            if($v["pinpai"])
                $pro[$k]["brand_name"]=db("pro_brands")->where(["id"=>$v["pinpai"]])->value("brand_name");
            else
                $pro[$k]["brand_name"]="";
            if(isset($v["pinlei"])){
                $pro[$k]["category_name"]=db("pro_category")->where(["id"=>$v["pinlei"]])->value("category_name");
            }
            if(isset($v["xilie"])){
                $pro[$k]["series_name"]=db("pro_series")->where(["id"=>$v["xilie"]])->value("series_name");
            }
        }
       // print_r($pro);
        $sumpro=0;
        for($i=0;$i<count($pro);$i++){
            $sumpro+=$pro[$i]["f"];
        }
        $this->assign("sumpro",$sumpro);
        $this->assign("pro",$pro);
        $this->assign("prode",$prode);
        //区域客户
        $client=Db::table("target_net_detail")->query("select t.wangdian,j.title as jxs,s.title as sys,c.title as net,sum(feiyong) as f  from target_net_detail as t inner join cg_jxs as j on t.jingxiao=j.id inner join cg_system as s on t.xitong=s.id inner join cg_net as c on t.wangdian=c.id where t.apply_id=$tid and t.jingxiao is not null group by t.wangdian");
        if($model["erjikaoheqi"]){
            $clientde=Db::table("target_net_detail")->query("select c.id as cainian,erjikaoheqi as yijikaoheqi,wangdian,sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id where t.apply_id=$tid and t.jingxiao is not null group by wangdian,t.erjikaoheqi");
        }else{
            $clientde=Db::table("target_net_detail")->query("select c.id as cainian,yijikaoheqi,wangdian,sum(feiyong) as f  from target_net_detail as t inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id inner join cg_cainian as c on k1.cid=c.id  where t.apply_id=$tid and t.jingxiao is not null group by wangdian,t.yijikaoheqi");
        }   
       
        $sumclient=0;
        for($i=0;$i<count($clientde);$i++){
            $sumclient+=$clientde[$i]["f"];
           
        }
        $this->assign("client",$client);
        $this->assign("clientde",$clientde);
        $this->assign("sumclient",$sumclient);
        if($apply["state2"]==1)
            $logs=$this->getlog(25,$tid);
        else
             $logs=$this->getlog(26,$tid);
         $this->assign("logs",$logs);
         if($apply["state2"]==2){
             $yuandetail=Db::table("target_net_detail")->alias("t")
              ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
            ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
            ->join("cg_cainian cn","k1.cid=cn.id","LEFT")
           
            ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
            ->join("pro_category pc","t.pinlei=pc.id","LEFT")
            ->join("pro_series ps","t.xilie=ps.id","LEFT")
            ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
            ->join("cg_system s","t.xitong=s.id","LEFT")
            ->join("cg_net n","t.wangdian=n.id","LEFT")
          
            ->where(["t.apply_id"=>$apply["yuanid"]])
            ->field("s.title as xitong,n.title as net,j.title as jingxiao,n.title as wangdian,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie")
            ->select();
            $tongjidata=[];
            for($i=0;$i<count($detail);$i++){
                $tongji=$detail[$i];
                $tongji["yuanfeiyong"]=0;
                
                for($j=0;$j<count($yuandetail);$j++){
                    if($detail[$i]["xitong"]==$yuandetail[$j]["xitong"]&&$detail[$i]["wangdian"]==$yuandetail[$j]["wangdian"]&&$detail[$i]["yijikaoheqi"]==$yuandetail[$j]["yijikaoheqi"]&&$detail[$i]["erjikaoheqi"]==$yuandetail[$j]["erjikaoheqi"]&&$detail[$i]["cainian"]==$yuandetail[$j]["cainian"]&&$detail[$i]["jingxiao"]==$yuandetail[$j]["jingxiao"]&&$detail[$i]["pinpai"]==$yuandetail[$j]["pinpai"]&&$detail[$i]["pinlei"]==$yuandetail[$j]["pinlei"]&&$detail[$i]["xilie"]==$yuandetail[$j]["xilie"]){
                        //内容相似
                        $tongji["yuanfeiyong"]=$yuandetail[$j]["feiyong"];
                        array_splice($yuandetail, $j, 1);break;
                    }
                }
                $tongji["duibi"]=$tongji["feiyong"]-$tongji["yuanfeiyong"];
                $tongjidata[]=$tongji;
            }
            for($i=0;$i<count($yuandetail);$i++){
                $tongji=["xitong"=>$yuandetail[$i]["xitong"],"wangdian"=>$yuandetail[$i]["wangdian"],"cainian"=>$yuandetail[$i]["cainian"],"yijikaoheqi"=>$yuandetail[$i]["yijikaoheqi"],"erjikaoheqi"=>$yuandetail[$i]["erjikaoheqi"],"feiyong"=>0,"yuanfeiyong"=>$yuandetail[$i]["feiyong"],"duibi"=>0-$yuandetail[$i]["feiyong"],"jingxiao"=>$yuandetail[$i]["jingxiao"],"pinpai"=>$yuandetail[$i]["pinpai"],"pinlei"=>$yuandetail[$i]["pinlei"],"xilie"=>$yuandetail[$i]["xilie"]];
                $tongjidata[]=$tongji;
            }
            $this->assign("tongjidata",$tongjidata);
        }
        return $this->fetch();
    }
    //导出
     public function daochu($tid){
         $apply=Db::table("target_net_apply")->where(["id"=>$tid])->find();
         $file_path=$apply["path"];$file_name="网点目标-".$apply["title"].".xls";
         header("Content-type:text/html;charset=utf-8"); 
         
           
            if(!file_exists($file_path)){ 
            echo "没有该文件文件"; 
            return ; 
            } 
            $fp=fopen($file_path,"r"); 
            $file_size=filesize($file_path); 
            //下载文件需要用到的头 
            Header("Content-type: application/octet-stream"); 
            Header("Accept-Ranges: bytes"); 
            Header("Accept-Length:".$file_size); 
            Header("Content-Disposition: attachment; filename=".$file_name); 
               ob_clean();
            flush();
            $buffer=1024; 
            $file_count=0; 
            //向浏览器返回数据 
            while(!feof($fp) && $file_count<$file_size){ 
            $file_con=fread($fp,$buffer); 
            $file_count+=$buffer; 
            echo $file_con; 
            } 
            fclose($fp); 
     }
    public function yd_jt_add(){
        $request = Request::instance();

        
        $data=$request->post();
        //print_r($data);exit;
        $target=Db::table("target_net_apply")->where(["id"=>$data['tid']])->find();
        $biaoti=$target["title"];
        $remark=trim(htmlspecialchars($data["remark"]));
        $state=trim($data["type"]);
        //$shenpiren=trim($data["shenpiren"]);
        $file = request()->file('wenjian');
        $tid=$data['tid'];
        
        $fujian=null;
        if($file){
            $info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/Jituan' . DS );
            if($info){
                // 成功上传后 获取上传信息
               
                // 输出
                $savename= $info->getSaveName();
                $path=ROOT_PATH . 'public/uploads/Jituan/'.$savename;
                $res=$this->excel_yidong($state,$path,$tid,$remark);
                return $res;

                
            }else{
                // 上传失败获取错误信息
                return json_encode(["msg"=>$file->getError(),"state"=>2]);
               
            }
        }else{
             return json_encode(["msg"=>"文件不能为空","state"=>2]);
        }

    }
    public function xg_jt_add(){
        $request = Request::instance();

        
        $data=$request->post();
        //print_r($data);exit;
        $target=Db::table("target_net_apply")->where(["id"=>$data['tid']])->find();
        $biaoti=trim($data["title"]);
        $remark=trim(htmlspecialchars($data["remark"]));
        $state=trim($data["type"]);
        //$shenpiren=trim($data["shenpiren"]);
        $file = request()->file('wenjian');
        $tid=$data['tid'];
        
        $fujian=null;
        if($file){
            $info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/Jituan' . DS );
            if($info){
                // 成功上传后 获取上传信息
               
                // 输出
                $savename= $info->getSaveName();
                $path=ROOT_PATH . 'public/uploads/Jituan/'.$savename;
                $res=$this->excel_xiugai($state,$path,$tid,$biaoti,$remark);
                return $res;

                
            }else{
                // 上传失败获取错误信息
                return json_encode(["msg"=>$file->getError(),"state"=>2]);
               
            }
        }else{
             return json_encode(["msg"=>"文件不能为空","state"=>2]);
        }
    }
    public function excel_xiugai($state,$filename,$tid,$biaoti,$remark){
        //$title="AP费用预算格式";
        Vendor("PHPExcel");
        Vendor("PHPExcel.IOFactory");
        
        Vendor("PHPExcel.Reader.Excel5");
        //$filename=ROOT_PATH . 'public/a.xls';
        $objReader = PHPExcel_IOFactory::createReaderForFile($filename);
        $objPHPExcel = $objReader->load($filename);
        $sheet=$objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumm = $sheet->getHighestColumn(); // 取得总列数
        //获取基本格式
        //return 1;
        $yuan=db("target_net_apply")->where(["id"=>$tid])->find();
        $fid=$yuan["type_id"];
        $target=Db::table("target_net_format")
        ->where(["id"=>$fid])->find();

        $datatianxie=[];
      $datatianxie[]="client_id";
      $client="网点";
       
        if($target["product_id"]){
            $datatianxie[]="product_id";
            $tmp=Db::table("field")->where(["id"=>$target["product_id"]])->find();
            $product=$tmp["field_name"];
        }
        
       
            
       
        if($target["kaoheqi"]){
            
            $datatianxie[]="kaoheqi";
            $tmp=Db::table("field")->where(["id"=>$target["kaoheqi"]])->find();
            $kaoheqi=$tmp["field_name"];
        }
        
       
             $datatianxie[]="yusuan";
       
       //return json_encode($datatianxie);
       // print_r($datatianxie);exit;
        //$
       $alldata=[];
       $timenow=date("Ymd");
      
     
        $datata=["type_id"=>$fid,"state"=>1,"title"=>$biaoti,"path"=>$filename,"remark"=>$remark,"id"=>$tid];
        
        $conunt_de=0;
        for ($row = 3; $row <= $highestRow; $row++){//行数是以第1行开始
            if(!trim($sheet->getCell('A'.$row)->getValue())){
                break;
            }
            $conunt_de++;
            $strcountde=sprintf("%03d",$conunt_de);
             $adddata=["detail_no"=>$strcountde];
            for ($column = 0; $column < count($datatianxie); $column++) {//列数是以A列开始
                $c=chr($column+65);//echo $c;exit;
                $content=trim($sheet->getCell($c.$row)->getValue());
                switch ($datatianxie[$column]) {
                    
                    case 'client_id':
                    #客户
                        if($content){
                            if($client=="经销商"){
                                $tmpclient=Db::table("cg_jxs")->where(["title"=>$content,"isdel"=>0])->find();
                                if($tmpclient){
                                    $adddata["jingxiao"]=$tmpclient["id"];
                                }else{
                                     
                                    return json_encode(["msg"=>"经销商不存在或已删除","state"=>2]);
                                }
                            }else if($client=="系统"){
                                $tmpclient=Db::table("cg_system")->alias("s")
                                ->join("cg_jxs j","s.jxs=j.id")
                                ->where(["s.title"=>$content,"s.isdel"=>0,"j.isdel"=>0])
                                ->field("j.id as jxs,s.id as xitong")->find();
                                if($tmpclient){
                                    $adddata["jingxiao"]=$tmpclient["jxs"];
                                    $adddata["xitong"]=$tmpclient["xitong"];
                                }else{
                                     
                                    return json_encode(["msg"=>"系统不存或所属经销商已删除","state"=>2]);
                                }
                            }else if($client=="网点"){
                                $tmpclient=Db::table("cg_system")->alias("s")
                                ->join("cg_jxs j","s.jxs=j.id")
                                ->join("cg_net n","n.system_id=s.id")
                                ->where(["n.title"=>$content,"n.isdel"=>0,"j.isdel"=>0,"s.isdel"=>0])
                                ->field("j.id as jxs,s.id as xitong,n.id as net")->find();
                                if($tmpclient){
                                    $adddata["jingxiao"]=$tmpclient["jxs"];
                                    $adddata["xitong"]=$tmpclient["xitong"];
                                    $adddata["wangdian"]=$tmpclient["net"];
                                }else{
                                     
                                    return json_encode(["msg"=>"网点不存或所属经销商，系统已删除","state"=>2]);
                                }
                            }else{
                                //错误终止
                                
                                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
                            }
                        }else{
                            
                            return json_encode(["msg"=>$client."必须填写","state"=>2]);
                        }
                    break;
                    case 'product_id':
                    #产品纬度
                        if($content){
                            if($product=="品牌"){
                                $tmppro=Db::table("pro_brands")->where(["is_actived"=>1,"is_deleted"=>0,"brand_name"=>$content])
                                ->field("id")->find();
                                if($tmppro){
                                    $adddata["pinpai"]=$tmppro["id"];
                                }else{
                                    
                                    return json_encode(["msg"=>"品牌不存或已删除","state"=>2]);
                                }

                            }else if($product=="品类"){
                                $tmppro=Db::table("pro_category")->alias("c")
                                ->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"c.category_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0])
                                ->field("b.id as brand_name,c.id as category_name")->find();
                                if($tmppro){
                                    $adddata["pinpai"]=$tmppro["brand_name"];
                                    $adddata["pinlei"]=$tmppro["category_name"];
                                }else{
                                    
                                    return json_encode(["msg"=>"品类不存或所属品牌已删除","state"=>2]);
                                }
                            }else if($product=="系列"){
                                $tmppro=Db::table("pro_series")->alias("s")
                                ->join("pro_category c","s.category_id=c.id")
                                ->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"s.series_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0,"s.is_actived"=>1,"s.is_deleted"=>0])
                                ->field("b.id as brand_name,c.id as category_name,s.id as series_name")->find();
                                if($tmppro){
                                    $adddata["pinpai"]=$tmppro["brand_name"];
                                    $adddata["pinlei"]=$tmppro["category_name"];
                                    $adddata["xilie"]=$tmppro["series_name"];
                                }else{
                                    
                                    return json_encode(["msg"=>"系列不存或所属品牌或品类已删除","state"=>2]);
                                }
                            }else{
                                //错误终止
                                // Db::rollBack();
                                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
                            }
                        }else{
                             
                            return json_encode(["msg"=>$product."必须填写","state"=>2]);
                        }
                    break;
                    
                
                    case "kaoheqi":
                    #考核期
                    if($content){
                        if($kaoheqi=="一级考核期"){
                            $tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")
                            ->join("cg_cainian c","k1.cid=c.id")->where(["k1.title"=>$content,"c.id"=>$target["cainian"]])->field("k1.id as title1")->find();
                            if($tmpkaohe){
                                $adddata["yijikaoheqi"]=$tmpkaohe["title1"];
                            }else{
                                 
                                return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
                            }
                        }else if($kaoheqi=="二级考核期"){
                            $tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")
                            ->join("cg_cainian c","k1.cid=c.id")->where(["k2.title"=>$content,"c.id"=>$target["cainian"]])->field("k1.id as title1,k2.id as title2")->find();
                            if($tmpkaohe){
                                $adddata["yijikaoheqi"]=$tmpkaohe["title1"];
                                $adddata["erjikaoheqi"]=$tmpkaohe["title2"];
                            }else{
                                
                                return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
                            }
                        }else{
                            
                            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
                        }
                    }else{
                        
                        return json_encode(["msg"=>$kaoheqi."必须填写","state"=>2]);
                    }
                    break;
                    
                    case 'yusuan':
                    if($content){
                        $adddata["feiyong"]=$content;
                    }else{
                        
                        return json_encode(["msg"=>"预算必须填写","state"=>2]);
                    }
                    break;
                    default:
                        # code...
                        break;
                }
                
                //$dataset[] = $sheet->getCell($c.$row)->getValue();
                //echo $c.$row.":".$sheet->getCell($c.$row)->getValue()."<br />";
            }
            $alldata[]=$adddata;
        }
          Db::startTrans();
       try{
            $res2=Db::table("target_net_apply")->update($datata);
            for($i=0;$i<count($alldata);$i++){
                $alldata[$i]["apply_id"]=$tid;
            }
            Db::table("target_net_detail")->where(["apply_id"=>$tid])->delete();
            $res1=Db::table("target_net_detail")->insertAll($alldata);
            //Db::table("target_net_apply")->where(["id"=>$tid])->update(["tag"=>2,"nextid"=>$res2,"islock"=>1]);
        }catch(\Exception $e){

                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
            if($res1&&$res2){
                Db::commit(); //事物提交
                if($state==3){
                    if($yuan["state2"]==2)
                        $this->gowork(26,$res2);
                    else
                        $this->gowork(25,$res2);
                }
               return json_encode(["msg"=>"操作已成功","state"=>1]);
            }else{
                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
        //print_r($alldata);
    }
    public function excel_yidong($state,$filename,$tid,$remark){
        //$title="AP费用预算格式";
        Vendor("PHPExcel");
        Vendor("PHPExcel.IOFactory");
        
        Vendor("PHPExcel.Reader.Excel5");
        //$filename=ROOT_PATH . 'public/a.xls';
        $objReader = PHPExcel_IOFactory::createReaderForFile($filename);
        $objPHPExcel = $objReader->load($filename);
        $sheet=$objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumm = $sheet->getHighestColumn(); // 取得总列数
        //获取基本格式
        //return 1;
        $yuan=db("target_net_apply")->where(["id"=>$tid])->find();
        $fid=$yuan["type_id"];
        $target=Db::table("target_net_format")
        ->where(["id"=>$fid])->find();

        $datatianxie=[];
      $datatianxie[]="client_id";
      $client="网点";
       
        if($target["product_id"]){
            $datatianxie[]="product_id";
            $tmp=Db::table("field")->where(["id"=>$target["product_id"]])->find();
            $product=$tmp["field_name"];
        }
        
       
            
       
        if($target["kaoheqi"]){
            
            $datatianxie[]="kaoheqi";
            $tmp=Db::table("field")->where(["id"=>$target["kaoheqi"]])->find();
            $kaoheqi=$tmp["field_name"];
        }
        
       
             $datatianxie[]="yusuan";
       
       //return json_encode($datatianxie);
       // print_r($datatianxie);exit;
        //$
       $alldata=[];
       $timenow=date("Ymd");
       $num=Db::table("target_net_apply")->where("no like '$timenow%'")->count();
       $num++;
       $strnum=$timenow.sprintf("%05d", $num);
     
        $datata=["type_id"=>$fid,"c_users_id"=>Session::get("login_id"),"created_on"=>date("Y-m-d H:i:s"),"state"=>1,"title"=>$yuan["title"],"path"=>$filename,"no"=>$strnum,"state2"=>2,"yuanid"=>$tid,"remark"=>$remark];
        
        $conunt_de=0;
        for ($row = 3; $row <= $highestRow; $row++){//行数是以第1行开始
            if(!trim($sheet->getCell('A'.$row)->getValue())){
                break;
            }
            $conunt_de++;
            $strcountde=sprintf("%03d",$conunt_de);
             $adddata=["detail_no"=>$strcountde];
            for ($column = 0; $column < count($datatianxie); $column++) {//列数是以A列开始
                $c=chr($column+65);//echo $c;exit;
                $content=trim($sheet->getCell($c.$row)->getValue());
                switch ($datatianxie[$column]) {
                    
                    case 'client_id':
                    #客户
                        if($content){
                            if($client=="经销商"){
                                $tmpclient=Db::table("cg_jxs")->where(["title"=>$content,"isdel"=>0])->find();
                                if($tmpclient){
                                    $adddata["jingxiao"]=$tmpclient["id"];
                                }else{
                                     
                                    return json_encode(["msg"=>"经销商不存在或已删除","state"=>2]);
                                }
                            }else if($client=="系统"){
                                $tmpclient=Db::table("cg_system")->alias("s")
                                ->join("cg_jxs j","s.jxs=j.id")
                                ->where(["s.title"=>$content,"s.isdel"=>0,"j.isdel"=>0])
                                ->field("j.id as jxs,s.id as xitong")->find();
                                if($tmpclient){
                                    $adddata["jingxiao"]=$tmpclient["jxs"];
                                    $adddata["xitong"]=$tmpclient["xitong"];
                                }else{
                                     
                                    return json_encode(["msg"=>"系统不存或所属经销商已删除","state"=>2]);
                                }
                            }else if($client=="网点"){
                                $tmpclient=Db::table("cg_system")->alias("s")
                                ->join("cg_jxs j","s.jxs=j.id")
                                ->join("cg_net n","n.system_id=s.id")
                                ->where(["n.title"=>$content,"n.isdel"=>0,"j.isdel"=>0,"s.isdel"=>0])
                                ->field("j.id as jxs,s.id as xitong,n.id as net")->find();
                                if($tmpclient){
                                    $adddata["jingxiao"]=$tmpclient["jxs"];
                                    $adddata["xitong"]=$tmpclient["xitong"];
                                    $adddata["wangdian"]=$tmpclient["net"];
                                }else{
                                     
                                    return json_encode(["msg"=>"网点不存或所属经销商，系统已删除","state"=>2]);
                                }
                            }else{
                                //错误终止
                                
                                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
                            }
                        }else{
                            
                            return json_encode(["msg"=>$client."必须填写","state"=>2]);
                        }
                    break;
                    case 'product_id':
                    #产品纬度
                        if($content){
                            if($product=="品牌"){
                                $tmppro=Db::table("pro_brands")->where(["is_actived"=>1,"is_deleted"=>0,"brand_name"=>$content])
                                ->field("id")->find();
                                if($tmppro){
                                    $adddata["pinpai"]=$tmppro["id"];
                                }else{
                                    
                                    return json_encode(["msg"=>"品牌不存或已删除","state"=>2]);
                                }

                            }else if($product=="品类"){
                                $tmppro=Db::table("pro_category")->alias("c")
                                ->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"c.category_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0])
                                ->field("b.id as brand_name,c.id as category_name")->find();
                                if($tmppro){
                                    $adddata["pinpai"]=$tmppro["brand_name"];
                                    $adddata["pinlei"]=$tmppro["category_name"];
                                }else{
                                    
                                    return json_encode(["msg"=>"品类不存或所属品牌已删除","state"=>2]);
                                }
                            }else if($product=="系列"){
                                $tmppro=Db::table("pro_series")->alias("s")
                                ->join("pro_category c","s.category_id=c.id")
                                ->join("pro_brands b","c.brand_id=b.id")->where(["c.is_actived"=>1,"c.is_deleted"=>0,"s.series_name"=>$content,"b.is_actived"=>1,"b.is_deleted"=>0,"s.is_actived"=>1,"s.is_deleted"=>0])
                                ->field("b.id as brand_name,c.id as category_name,s.id as series_name")->find();
                                if($tmppro){
                                    $adddata["pinpai"]=$tmppro["brand_name"];
                                    $adddata["pinlei"]=$tmppro["category_name"];
                                    $adddata["xilie"]=$tmppro["series_name"];
                                }else{
                                    
                                    return json_encode(["msg"=>"系列不存或所属品牌或品类已删除","state"=>2]);
                                }
                            }else{
                                //错误终止
                                // Db::rollBack();
                                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
                            }
                        }else{
                             
                            return json_encode(["msg"=>$product."必须填写","state"=>2]);
                        }
                    break;
                    
                
                    case "kaoheqi":
                    #考核期
                    if($content){
                        if($kaoheqi=="一级考核期"){
                            $tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")
                            ->join("cg_cainian c","k1.cid=c.id")->where(["k1.title"=>$content,"c.id"=>$target["cainian"]])->field("k1.id as title1")->find();
                            if($tmpkaohe){
                                $adddata["yijikaoheqi"]=$tmpkaohe["title1"];
                            }else{
                                 
                                return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
                            }
                        }else if($kaoheqi=="二级考核期"){
                            $tmpkaohe=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")
                            ->join("cg_cainian c","k1.cid=c.id")->where(["k2.title"=>$content,"c.id"=>$target["cainian"]])->field("k1.id as title1,k2.id as title2")->find();
                            if($tmpkaohe){
                                $adddata["yijikaoheqi"]=$tmpkaohe["title1"];
                                $adddata["erjikaoheqi"]=$tmpkaohe["title2"];
                            }else{
                                
                                return json_encode(["msg"=>$kaoheqi."不存在或财年不符","state"=>2]);
                            }
                        }else{
                            
                            return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
                        }
                    }else{
                        
                        return json_encode(["msg"=>$kaoheqi."必须填写","state"=>2]);
                    }
                    break;
                    
                    case 'yusuan':
                    if($content){
                        $adddata["feiyong"]=$content;
                    }else{
                        
                        return json_encode(["msg"=>"预算必须填写","state"=>2]);
                    }
                    break;
                    default:
                        # code...
                        break;
                }
                
                //$dataset[] = $sheet->getCell($c.$row)->getValue();
                //echo $c.$row.":".$sheet->getCell($c.$row)->getValue()."<br />";
            }
            $alldata[]=$adddata;
        }
          Db::startTrans();
       try{
            $res2=Db::table("target_net_apply")->insertGetId($datata);
            for($i=0;$i<count($alldata);$i++){
                $alldata[$i]["apply_id"]=$res2;
            }
            $res1=Db::table("target_net_detail")->insertAll($alldata);
            Db::table("target_net_apply")->where(["id"=>$tid])->update(["tag"=>2,"nextid"=>$res2,"islock"=>1]);
        }catch(\Exception $e){

                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
            if($res1&&$res2){
                Db::commit(); //事物提交
                if($state==3){
                    $this->gowork(26,$res2);
                }
               return json_encode(["msg"=>"操作已成功","state"=>1]);
            }else{
                Db::rollBack();
                return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
        //print_r($alldata);
    }
}